1. Field of the Invention
The present invention relates to a method, system, and program for implementing a database trigger.
2. Description of the Related Art
Data records in a relational database management system (RDBMS) are maintained in tables, which are a collection of rows all having the same columns. Each column maintains information on a particular type of data for the data records which comprise the rows. Certain database programs, such as the International Business Machines Corporation (xe2x80x9cIBMxe2x80x9d) DB2 database program, include a trigger feature. A trigger defines a set of actions to perform when the database program modifies data in a specified database table. Triggers are used to perform functions such as validation of input data, automatically generate a value for a newly inserted row, read from other tables for cross-referencing purposes, write to other tables for audit-trail purposes, and support alerts through electronic mail messages. Triggers permit faster application development, global enforcement of business rules, and easier maintenance of applications and data.
Triggers are optional and are defined using the CREATE TRIGGER statement. A trigger may be defined with the following criteria. A xe2x80x9cbase tablexe2x80x9d is the table for which the trigger is defined. The xe2x80x9ctrigger eventxe2x80x9d defines a specific SQL operation that modifies the base table. The xe2x80x9ctrigger eventxe2x80x9d may comprise, for example, a delete, insert or update operation with respect to one or more columns of the base table. The xe2x80x9ctrigger activation timexe2x80x9d defines, for example, whether the trigger should be activated before or after the trigger event is performed on the base table. The xe2x80x9ctriggered actionxe2x80x9d consists of an optional search condition and a set of SQL statements that are executed whenever the trigger event occurs.
The triggered action may operate on data from columns in the set of affected rows of the base table. Data from the set of affected rows in the base table are carried over to the triggered action through the use of transition variables. Transition variables use the names of the columns in the base table qualified by a specified name that identifies whether the reference is to the old value (prior to the update) or the new value (after the update). The new data can also be modified when subject to the triggered action. For instance, upon the occurrence of the trigger event, data from the base table subject to the SQL triggering action may be written to another table. In such case, the data to copy over as part of the triggered action is stored in a transition variable. This transition variable is then provided to the SQL update statement that implements the triggered action. Transition variables maintain their data in a work file. Further details of a database trigger are described in the IBM publication, xe2x80x9cIBM DB2 Universal Database: SQL Reference, Version 6,xe2x80x9d IBM document no. SC09-2847-00 (Copyright IBM, 1999), which publication is incorporated herein by reference in its entirety.
A large object (LOB) is a data type that contains large amounts of data, such as images, movies, audio, etc. In the prior art, a large object (LOB) that is a transition variable cannot be stored in the same work file that stores non-LOB transition variables due to the potentially large size of the large object (LOB) data.
Thus, there is a need in the art for improved techniques for maintaining large objects (LOBs) as transition variables.
Provided is a method, system, and program for implementing a database trigger. Upon detecting a trigger event, a determination is made of at least one row in a base table affected by the trigger event. For each column in the base table of a data type that is referenced in a triggered action associated with the trigger event, a reference is generated referencing the data of the data type. The reference is inserted in a transition table column including data of the data type from the affected row in the base table referenced in the triggered action. The reference is used to access the data of the data type when performing the triggered action.
In further implementations, the reference includes a row identifier and version number of the data in the base table column referenced by the triggered action.
Still further, the data type for which the reference is generated comprises a large object data type. In such case, the reference is used to access the large object data by accessing an auxiliary table storing large object data for the base table column referenced by the triggered action. The row identifier and the version number in the reference are used to access the large object in the auxiliary table. The row identifier and version number identify the location of the large object data in the auxiliary table.
In the described implementations, a reference to data of a particular data type, such as a large object (LOB) data type, is stored in a transition table used by the triggered action instead of the actual data itself to reduce the space in the transition table needed to represent the LOB data and to maximize the number of transition variable rows that may be maintained in the transition table.